package com.yuri.news.jpa;

import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import com.yuri.news.entity.User;

/**
 * 用户jpa
 * @author Yuri
 */
public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
	/**
	 * 根据登录名进行查询
	 * @param name
	 * @return
	 */
	public User findByUserName(String userName);
	
	/**
	 * 根据用户名和密码进行查询
	 * @param name
	 * @param pw
	 * @return
	 */
	User findByUserNameAndUserPw(String userName, String userPw);
	
	/**
	 * 查询用户
	 * @param isAva		// 是否有效
	 * @return
	 */
	List<User> findByIsAvailable(Boolean isAva);
	
	/**
	 * 查询有效用户（分页查询）
	 * @param page		// 分页信息
	 * @param isAva		// 是否可用
	 * @return
	 */
	Page<User> findByIsAvailable(Pageable page,Boolean isAva);
	
	/**
	 * 根据登录名进行模糊查询
	 * @param userName		用户名
	 * @param isAva			是否可用
	 * @return
	 */
	List<User> findByUserNameLikeAndIsAvailable(String userName, Boolean isAva);
	
	/**
	 * 根据登录名进行模糊查询（分页）
	 * @param page 			分页信息
	 * @param userName		用户名
	 * @param isAva			是否可用
	 * @return
	 */
	Page<User> findByUserNameLikeAndIsAvailable(Pageable page,String userName, Boolean isAva);
	
	/**
	 * 根据部门名查询部门下的用户(不分页)
	 * @param depaName
	 * @return
	 */
	@Query(value = "SELECT * FROM t_user INNER JOIN t_depa ON t_user.depa_id = t_depa.id where t_depa.depa_name=?1 and t_user.is_available = ?2", nativeQuery = true)
	List<User> findByDepaName(String depaName,Boolean isAva);
	
	/**
	 * 根据部门名查询部门下的用户(分页)
	 * @param depaName		部门名称
	 * @param isAva			是否可用
	 * @param page			分页信息
	 * @return
	 */
	@Query(value = "SELECT * FROM t_user INNER JOIN t_depa ON t_user.depa_id = t_depa.id where t_depa.depa_name=?1 and t_user.is_available = ?2",
			countQuery = "SELECT count(*) FROM t_user INNER JOIN t_depa ON t_user.depa_id = t_depa.id where t_depa.depa_name=?1 and t_user.is_available = ?2",
			nativeQuery = true)
	Page<User> findByDepaName(String depaName, Boolean isAva, Pageable page);
	
	/**
	 * 根据角色名查询拥有此角色的用户
	 * @param roleName		角色名
	 * @param isAva			是否可用
	 * @return
	 */
	@Query(value = "SELECT user.* FROM t_user as user, t_user_role as ur, t_role as role where role.role_name = ?1 and role.id = ur.role_id and ur.user_id = user.id and user.is_available = ?2", nativeQuery = true)
	List<User> findByRoleName(String roleName,Boolean isAva);
	
	/**
	 * 根据角色名查询拥有此角色的用户（分页）
	 * @param roleName		角色名
	 * @param isAva			是否为可用
	 * @param page			分页信息
	 * @return
	 */
	@Query(value = "SELECT user.* FROM t_user as user, t_user_role as ur, t_role as role where role.role_name = ?1 and role.id = ur.role_id and ur.user_id = user.id and user.is_available = ?2",
			countQuery = "SELECT count(*) FROM t_user as user, t_user_role as ur, t_role as role where role.role_name = ?1 and role.id = ur.role_id and ur.user_id = user.id and user.is_available = ?2",
			nativeQuery = true)
	Page<User> findByRoleName(String roleName, Boolean isAva, Pageable page);

}
